import pandas as pd


def read_excel_file(file_path):
    """
    读取 Excel 文件并返回指定工作表中的数据
    :param file_path: Excel 文件路径
    :return: 工作表数据 DataFrame
    """
    try:
        excel_file = pd.ExcelFile(file_path)
        df = excel_file.parse('Sheet1')
        return df
    except FileNotFoundError:
        print(f"文件 {file_path} 未找到。")
        return None
    except KeyError:
        print("工作表 'Sheet1' 不存在。")
        return None


def count_by_gender(df):
    """
    按性别统计学生人数
    :param df: 学生数据 DataFrame
    :return: 性别统计结果 Series
    """
    if df is not None:
        return df['性别'].value_counts()
    return None


def count_by_age_group(df):
    """
    按年龄段统计学生人数
    :param df: 学生数据 DataFrame
    :return: 年龄段统计结果 Series
    """
    if df is not None:
        bins = [0, 20, 30, 40, 50, 100]
        labels = ['20岁及以下', '21 - 30岁', '31 - 40岁', '41 - 50岁', '50岁以上']
        df['年龄段'] = pd.cut(df['年龄'], bins=bins, labels=labels)
        return df['年龄段'].value_counts()
    return None


def count_by_area(df):
    """
    按地区（外省按省份，海南省按市县）统计学生人数
    :param df: 学生数据 DataFrame
    :return: 地区统计结果 Series
    """
    def get_area(address):
        if '海南省' in address:
            return address.split('省')[1].split('市')[0] if '市' in address else address.split('省')[1].split('县')[0]
        else:
            return address.split('省')[0] if '省' in address else address.split('市')[0]

    if df is not None:
        df['地区'] = df['家庭地址'].apply(get_area)
        return df['地区'].value_counts()
    return None


def main():
    file_path = 'D:\\xs\\student.xlsx'
    df = read_excel_file(file_path)

    gender_count = count_by_gender(df)
    if gender_count is not None:
        print('按性别统计学生人数：')
        print(gender_count)

    age_group_count = count_by_age_group(df)
    if age_group_count is not None:
        print('\n按年龄段统计学生人数：')
        print(age_group_count)

    area_count = count_by_area(df)
    if area_count is not None:
        print('\n按地区统计学生人数：')
        print(area_count)


if __name__ == "__main__":
    main()